package com.h3c.nem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.h3c.nem.entity.TbOfferStudy;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * 学历信息数据库访问层
 *
 * @author chenyifan
 * @since 2022-06-06 11:42:52
 */
public interface TbOfferStudyMapper extends BaseMapper<TbOfferStudy> {

    @Insert(" INSERT INTO tb_offer_study \n" +
            "(study_id, worker_id, offer_id, college_name, major_name,\n" +
            "begin_date, end_date, study_type, education, degree, grant_date, \n" +
            "education_no, degree_no, is_highest_education, is_highest_degree, \n" +
            "education_no_state, degree_no_state, TYPE, is_first_study, major_name2, education_file_id, \n" +
            "degree_file_id, education_file_path, degree_file_path, update_time, already_save, \n" +
            "education_file_name, degree_file_name, is_beisen_data) \n" +
            "SELECT \n" +
            "nextval('hrssseq'),a.HC_NO,b.OfferID,b.CollegesName,b.MajorName, \n" +
            "CASE WHEN b.BeginDate='' THEN STR_TO_DATE('9999-01-01','%Y-%m-%d') WHEN length(b.BeginDate) != 10 THEN STR_TO_DATE('9999-01-01','%Y-%m-%d') ELSE STR_TO_DATE(b.BeginDate,'%Y-%m-%d') END, \n" +
            "CASE WHEN b.EndDate='' THEN STR_TO_DATE('9999-01-01','%Y-%m-%d') WHEN length(b.EndDate) != 10 THEN STR_TO_DATE('9999-01-01','%Y-%m-%d') ELSE STR_TO_DATE(b.EndDate,'%Y-%m-%d') END, \n" +
            "CASE WHEN b.StudyTypeCode = '' THEN '' WHEN b.StudyTypeCode is NULL THEN '' ELSE b.StudyTypeCode END, \n" +
            "CASE WHEN b.EducationCode = '' THEN '' WHEN b.EducationCode is NULL THEN '' ELSE b.EducationCode END, CASE WHEN b.DegreeCode = '' THEN '' WHEN b.DegreeCode is NULL THEN '' ELSE b.DegreeCode END, \n" +
            "CASE WHEN b.GrantDate='' THEN NULL WHEN length(b.GrantDate) != 10 THEN NULL WHEN b.GrantDate LIKE '%.%' THEN NULL " +
            "WHEN b.GrantDate LIKE '%/%' THEN NULL ELSE STR_TO_DATE(b.GrantDate,'%Y-%m-%d') END, \n" +
            " b.EducationNo,  b.DegreeNo, \n" +
            "CASE WHEN b.IsHighestDegree = '' THEN NULL WHEN b.IsHighestDegree = '1' THEN 0 WHEN b.IsHighestDegree = '0' THEN 1 END, \n" +
            "CASE WHEN b.IsRealHighestDegree = '' THEN NULL WHEN b.IsRealHighestDegree = '1' THEN 0 WHEN b.IsRealHighestDegree = '0' THEN 1 END, \n" +
            "b.EducationNoState, b.DegreeNoState, b.Type, \n" +
            "CASE WHEN b.IsFirstStudy = '' THEN NULL WHEN b.IsFirstStudy = '1' THEN 0 WHEN b.IsFirstStudy = '0' THEN 1 END, \n" +
            "b.MajorName2, NULL, NULL, NULL, NULL, NOW(), 0, NULL, NULL, 1 \n" +
            "FROM Offer_Send_Detail a, Offer_StudyInfo b \n" +
            "WHERE a.id= b.OfferID")
    void initStudyRecordFromHrss();

    @Update("UPDATE tb_offer_study SET education_file_id = #{educationFileId}, education_file_name = #{educationFileName}, education_file_path = #{educationFilePath} WHERE study_id = #{studyId}")
    void initEducationRecordFromHrss(TbOfferStudy tbOfferStudy);

    @Update("UPDATE tb_offer_study SET degree_file_id = #{degreeFileId}, degree_file_name = #{degreeFileName}, degree_file_path = #{degreeFilePath} WHERE study_id = #{studyId}")
    void initDegreeRecordFromHrss(TbOfferStudy tbOfferStudy);

}

